﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;

namespace PGMapper
{
    class PostgresSchema
    {
        public List<PostgresTable> Tables { get; private set; }

        PostgresSchema()
        {
            this.Tables = new List<PostgresTable>();
        }

        public static PostgresSchema FromDatabase(PostgresDatabase db)
        {
            Dictionary<string, PostgresTable> tables = PostgresTable.FromDatabase(db).ToDictionary(t => t.TableName);

            foreach (PostgresColumn column in PostgresColumn.FromDatabase(db))
                tables[column.TableName].Columns.Add(column);

            foreach (PostgresConstraint constraint in PostgresConstraint.FromDatabase(db))
                tables[constraint.TableName].Constraints.Add(constraint);
            
            return new PostgresSchema { Tables = tables.Values.ToList() };
        }

        public static PostgresSchema FromAssembly(Assembly assembly)
        {
            return FromAssembly(assembly);
        }

        public static PostgresSchema FromAssemblies(params Assembly[] assemblies)
        {
            var tables = from assembly in assemblies
                         from type in assembly.GetTypes()
                         where type.IsMappedType()
                         select PostgresTable.FromMappedType(type.GetMappedType());

            return new PostgresSchema { Tables = tables.ToList() };
        }
    }

    class PostgresTable
    {
        public string TableName { get; set; }

        public PostgresTable()
        {
            this.Columns = new List<PostgresColumn>();
            this.Constraints = new List<PostgresConstraint>();
        }

        public List<PostgresColumn> Columns { get; private set; }
        public List<PostgresConstraint> Constraints { get; private set; }

        public static List<PostgresTable> FromDatabase(PostgresDatabase db)
        {
            return db.ExecuteList<PostgresTable>(@"
                select table_name from information_schema.tables
                where table_type = 'BASE TABLE' and table_schema not in ('pg_catalog','information_schema')
            ");
        }

        public static PostgresTable FromMappedType(IMappedType type)
        {
            return new PostgresTable
            {
                TableName = type.Table.Name,
                Columns = type.Columns.Select(property => PostgresColumn.FromMappedProperty(property)).ToList(),
                Constraints = PostgresConstraint.FromMappedType(type)
            };
        }

        public override bool Equals(object obj)
        {
            var table = obj as PostgresTable;
            return table != null ? table.TableName == this.TableName : base.Equals(obj);
        }

        public override int GetHashCode() { return TableName.GetHashCode(); }
        public override string ToString() { return TableName; }
    }

    class PostgresColumn
    {
        public string TableName { get; set; }
        public string ColumnName { get; set; }
        public PostgresDataType DataType { get; set; }
        public int? CharacterMaximumLength { get; set; }
        public string ColumnDefault { get; set; }

        public bool IsSerial { get { return ColumnDefault != null && ColumnDefault.Contains("nextval"); } }

        public static List<PostgresColumn> FromDatabase(PostgresDatabase db)
        {
            return db.ExecuteList<PostgresColumn>(@"
                select table_name, column_name, data_type, character_maximum_length, column_default, is_nullable
                from information_schema.columns where table_schema not in ('pg_catalog','information_schema') " +
                // don't include columns from views
                "and table_name in (select table_name from information_schema.tables where table_type = 'BASE TABLE')"
               );
        }

        public static PostgresColumn FromMappedProperty(MappedProperty property)
        {
            Type propertyType = property.Property.PropertyType;

            // strip Nullable<> box if any
            if (propertyType.IsGenericType && propertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
                propertyType = propertyType.GetGenericArguments()[0];

            return new PostgresColumn
            {
                TableName = property.Table.Name,
                ColumnName = property.Column.Name,
                DataType = PostgresTypeFromSystemType(propertyType),
                CharacterMaximumLength = propertyType == typeof(string) ? 1024 : (int?)null,
                ColumnDefault = property.Column.IsSerial ? "nextval" : null
            };
        }

        public static PostgresDataType PostgresTypeFromSystemType(Type type)
        {
            if (type.IsReference())
                return PostgresDataType.Int32;

            // enums are always mapped to strings in the DB
            if (type.IsEnum)
                type = typeof(string);
            
            return (PostgresDataType)Enum.Parse(typeof(PostgresDataType), type.Name);
        }

        public string GetDataTypeCreationSpecifier()
        {
            if (IsSerial) return "serial"; // shortcut for integer with corresponding new sequence

            return EnumValueAttribute.StringValue(DataType)
                + (DataType == PostgresDataType.String ? "(1024)" : "");
        }

        // Carries the information required to match for two columns to be considered equal
        string Descriptor
        {
            get { return string.Format("{0}.{1}::{2}{3}", TableName, ColumnName, DataType,
                IsSerial ? "|serial" : ""); }
        }

        public override bool Equals(object obj)
        {
            var column = obj as PostgresColumn;
            return column != null ? column.Descriptor == this.Descriptor : base.Equals(obj);
        }

        public override int GetHashCode() { return Descriptor.GetHashCode(); }
        public override string ToString() { return Descriptor; }
    }

    enum PostgresDataType
    {
        [EnumValue("boolean")]
        Boolean,
        //Byte, SByte, Char
        [EnumValue("numeric")]
        Decimal,
        [EnumValue("double precision")]
        Double,
        [EnumValue("real")]
        Single,
        [EnumValue("integer")]
        Int32,
        //UInt32
        [EnumValue("bigint")]
        Int64,
        [EnumValue("smallint")]
        Int16,
        [EnumValue("character varying", "text")]
        String,
        [EnumValue("timestamp without time zone", "timestamp with time zone")]
        DateTime
    }

    class PostgresConstraint
    {
        public string TableName { get; set; }
        public string ColumnName { get; set; }
        public string ConstraintName { get; set; }
        public ConstraintType ConstraintType { get; set; }
        public int OrdinalPosition { get; set; }
        public string ReferencedTableName { get; set; }
        public string ReferencedColumnName { get; set; }

        public static List<PostgresConstraint> FromDatabase(PostgresDatabase db)
        {
            return db.ExecuteList<PostgresConstraint>(@"
                select  
	                u.constraint_name, 
	                t.constraint_type,
	                t.table_name,
	                k.column_name,
	                u.referenced_column_name, 
	                u.table_name as referenced_table_name, 
	                k.ordinal_position
                from 
	                (select
		                column_name as referenced_column_name,
		                constraint_name,
		                table_name,
		                constraint_schema
	                from
	                    information_schema.constraint_column_usage) as u
                    join information_schema.table_constraints t using (constraint_name)
                    join information_schema.key_column_usage k using (constraint_name)
                where u.constraint_schema not in ('pg_catalog','information_schema');
            ");
        }

        public static List<PostgresConstraint> FromMappedType(IMappedType type)
        {
            var constraints = new List<PostgresConstraint>();
            
            // simulate primary key constraints
            foreach (MappedProperty property in type.Columns.Where(p => p.Column.IsPrimaryKey))
                constraints.Add(new PostgresConstraint
                {
                    TableName = type.Table.Name,
                    ColumnName = property.Column.Name,
                    ConstraintName = type.GetPrimaryKeyConstraintName(),
                    ConstraintType = ConstraintType.PrimaryKey
                });

            // unique constraints
            foreach (MappedProperty property in type.Columns.Where(p => p.Column.IsUnique))
                constraints.Add(new PostgresConstraint
                {
                    TableName = type.Table.Name,
                    ColumnName = property.Column.Name,
                    ConstraintName = property.GetUniqueConstraintName(),
                    ConstraintType = ConstraintType.Unique
                });

            // foreign key constraints
            foreach (MappedProperty property in type.Columns.Where(p => p.Property.PropertyType.IsReference()))
                constraints.Add(new PostgresConstraint
                {
                    TableName = type.Table.Name,
                    ColumnName = property.Column.Name,
                    ConstraintName = property.GetForeignKeyConstraintName(),
                    ConstraintType = ConstraintType.ForeignKey,
                    ReferencedTableName = property.Property.PropertyType.GetReferencedType().GetMappedType().Table.Name,
                    ReferencedColumnName = property.Property.PropertyType.GetReferencedType().GetMappedType().SerialColumn.Column.Name
                });

            return constraints;
        }

        // Carries the information required to match for two columns to be considered equal
        string Descriptor
        {
            get { return string.Format("{0}|{1}", ColumnName, ConstraintName); }
        }

        public override bool Equals(object obj)
        {
            var constraint = obj as PostgresConstraint;
            return constraint != null ? constraint.Descriptor == this.Descriptor : base.Equals(obj);
        }

        public override int GetHashCode() { return Descriptor.GetHashCode(); }
        public override string ToString() { return Descriptor; }
    }

    enum ConstraintType
    {
        [EnumValue("PRIMARY KEY")]
        PrimaryKey,
        [EnumValue("UNIQUE")]
        Unique,
        [EnumValue("FOREIGN KEY")]
        ForeignKey
    }
}
